class OracleController < ApplicationController

  require 'oci8'

  def to_generator(cursor)
    Enumerator.new do |enum|
      while r=cursor.fetch
        enum.yield r
      end
    end
  end

  #Test if connection is ok
  def test
    conn=OCI8.new('rism1','rism1','//rism-online.info:1521/orcl')
    sql='select kutit from hspr01 where rismnr is not NULL and rownum < 10'
    @result=to_generator(conn.exec(sql))
  end

  #Search for doublets of external XML-file in Oracle-Database
    #Helper table in Oracle with create table ss_dubl and indexes
    # create table ss_dubl as select h.rismnr, k.bvsigl,lower(regexp_replace(h.hsexsg, \'\s*\',\'\')), h.kutit from hspr01 h
   # inner join hss101 hs on (hs.pr_isn=h.h0001)
    # inner join ksprpd k on (hs.ksu200=k.k0001)
    # where h.rismnr is not NULL
  def findDoublets()
        conn=OCI8.new('rism1','rism1','//rism-online.info:1521/orcl')
    dn ='dn:http://www.loc.gov/MARC21/slim'
    @result=[]
    @fin=[]
    xml_document = LibXML::XML::Document.file('/home/stephan/RSQV/test.xml')
    book_xpath = '//dn:record'    
    xml_document.find(book_xpath,dn).each {|n|
      @result << [n.find('dn:controlfield[@tag="001"]',dn)[0].content,
        [n.find_first('dn:datafield[@tag="852"]/dn:subfield[@code="a"]', dn).content,
        n.find_first('dn:datafield[@tag="852"]/dn:subfield[@code="c"]', dn).content,
        n.find_first('dn:datafield[@tag="100"]/dn:subfield[@code="a"]', dn).content,
        ]]
    }

    @result.each {|n|
      shelfmark=n[1][1].downcase.gsub /\s/, ''
      sigl=n[1][0]
      sql='select rismnr, bvsigl,hsexsg, kutit from ss_dubl where hsexsg=\''+shelfmark+'\' and bvsigl=\''+sigl+'\''
      if shelfmark!='[ohne Signatur]'
        c=conn.exec(sql)
        @fin << [[n[0][0..20]+': ', sigl, shelfmark, '('+n[1][2]+')'], c.fetch]
        c.close
      end
    }

  end

end
